package modele;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.TreeSet;

public class DAO {

    static Collection<Med> getLesMeds() {
        Collection<Med> lesColDeMeds = new TreeSet<Med>();
        try {
            Connection con = Connect.get();
            Statement req;
            req = con.createStatement();
            ResultSet rs = req.executeQuery("SELECT *  FROM medecin");
            while (rs.next()) {
                Med m = new Med(rs.getString("nom"), rs.getString("prenom"), rs.getString("adresse"), rs.getString("tel"), rs.getString("specialitecomplementaire"), rs.getString("id"), rs.getString("departement"));
                lesColDeMeds.add(m);
            }
        } catch (SQLException e) {
            System.out.println("Erreur SQL :" + e);
        }
        return lesColDeMeds;
    }

    static Collection<Dep> getLesDeps() {
        Collection<Dep> lesColDeDeps = new TreeSet<Dep>();
        try {
            Connection con = Connect.get();
            Statement req;
            req = con.createStatement();
            ResultSet rs = req.executeQuery("SELECT DISTINCT departement FROM medecin");

            while (rs.next()) {
                Dep p = new Dep(rs.getString("departement"));
                lesColDeDeps.add(p);
            }
        } catch (SQLException e) {
            System.out.println("Erreur SQL :" + e);
        }
        return lesColDeDeps;
    }

    static Collection<Spe> getLesSpec() {

        Collection<Spe> lesColDeSpec = new TreeSet<Spe>();
        try {
            Connection con = Connect.get();
            Statement req;
            req = con.createStatement();
            ResultSet rs = req.executeQuery("SELECT DISTINCT specialitecomplementaire FROM medecin where specialitecomplementaire is not null");
            while (rs.next()) {
                Spe s = new Spe(rs.getString("specialitecomplementaire"));
                lesColDeSpec.add(s);
            }
        } catch (SQLException e) {
            System.out.println("Erreur SQL :" + e);
        }
        return lesColDeSpec;
    }
}